package net.atomarrow.util;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.MalformedURLException;
import java.net.URL;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import net.atomarrow.controllers.converter.TypeConverter;
import net.atomarrow.controllers.converter.TypeMismatchException;
import net.atomarrow.ioc.ApplicationContext;
import net.atomarrow.util.excel.CellData;
import net.atomarrow.util.excel.ExcelDatas;
import net.atomarrow.util.excel.ExcelFormatListener;
import net.atomarrow.util.excel.NullCellException;
import net.atomarrow.util.excel.SimpleHSSFCellStyle;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 * Excel工具类
 * @author Michael
 *
 */
public class ExcelUtil{



	/**
	 * 添加样式
	 * @param workbook
	 * @param cell 单元格
	 * @param CellStyle 单元格样式
	 */
	private static void addStyle(HSSFWorkbook workbook, HSSFCell cell, CellStyle CellStyle) {
		if(CellStyle==null)return;
		if(CellStyle instanceof SimpleHSSFCellStyle){
			SimpleHSSFCellStyle style = (SimpleHSSFCellStyle) CellStyle;
			HSSFCellStyle hstyle = workbook.createCellStyle();
			HSSFFont font = workbook.createFont();
			style.copyPropertiesToFont(font);
			style.copyPropertiesToStyle(hstyle);
			cell.setCellStyle(style.getStyle());
		}else{
			cell.setCellStyle(CellStyle);
		}
	}
	/**
	 * 从构建的HSSFWorkbook得到数据流
	 * @param workbook
	 * @return
	 */
	public static InputStream getWorkbookInputStream(HSSFWorkbook workbook){
		ByteArrayOutputStream os = new ByteArrayOutputStream();

		try {
			workbook.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		}
		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);
		return is;
	}
	/**
	 * 从文件得到数据流
	 * @param filePath
	 * @return
	 */
	public static InputStream getExcelStream(String filePath) {
		File f = new File(filePath);

		FileInputStream fis = null;
		try {
			fis = new FileInputStream(f);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return fis;
	}



	public <T> List<T> getListFromExcel(String path, Class<? extends T> clazz,
			String[] columns, boolean[] notNull, int startRow) throws Throwable {
		return getListFromExcel(path, clazz, columns, notNull, startRow, null);
	}
	private static boolean setValueToDomain(Class clazz, Object object, String fieldName,
			HSSFCell cell, TypeConverter typeConverter, boolean notNull, int currentRow, int currentCol, ExcelFormatListener listener)
			throws Throwable {
		final boolean isNull=true;
		final boolean noNull=false;
		Object value = null;
//		Field field = clazz.getDeclaredField(fieldName);
		Field field = clazz.getDeclaredField(fieldName.contains(".")?fieldName.split("\\.")[0]:fieldName);
		DecimalFormat df = new DecimalFormat("#.###");
		/*
		 * 处理未填项
		 */
		if(cell==null){
			if (notNull) {
				/*
				 * 如果为必填，不能为空的情况，实际单元格为空，则报异常
				 */
				return isNull;
			}
		}else{
			if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
				if (notNull) {
					return isNull;
				}
			} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
				value = cell.getStringCellValue().trim();
				if(value==null||value.equals("")){
					if (notNull) {
						return isNull;
					}
				}
			}else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
				value=cell.getBooleanCellValue();
			}
			else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC&&HSSFDateUtil.isCellDateFormatted(cell)) {
				value = DateUtil.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()),"yyyy-MM-dd HH:mm:ss");
			}else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
				value = df.format(cell.getNumericCellValue());
			} else {
				throw new Throwable("Excel文件中包含公式信息或者宏病毒感染，请重试！");
//				value =null;
//				if(value==null||value.equals("")){
//					if (notNull) {
//						return isNull;
//					}
//				}
			}
		}
		/*
		 * 根据用户定义改变值内容
		 */
		Object tmp;
		if(listener!=null){
			tmp=listener.changeValue(fieldName,value,currentRow,currentCol);
			if(tmp!=null){
				if(tmp instanceof Throwable){
					throw (Throwable)tmp;
				}
				value=tmp;
			}
		}
		/*
		 * 如果值为null则不处理赋值
		 */
		if(value==null)return noNull;

		/*
		 * 如果值为null则不处理赋值
		 */
		if(value==null)return noNull;
		Class type=field.getType();
		if(type==Map.class||type==HashMap.class){
			field.setAccessible(true);
			Map map = (Map) field.get(object);
			map.put(fieldName.split("\\.")[1], value);
		}else{
			value = typeConverter.converter(new Object[]{value}, field.getType());
			//field.setAccessible(true);
			//field.set(object, value);
			BeanUtil.setValueByMethod(object, field, value);
		}
		return noNull;
	}

	public static InputStream exportExcel(ExcelDatas excelDatas) {
		return getWorkbookInputStream(fillSheet(new HSSFWorkbook(),excelDatas));
	}

	public static InputStream exportExcel(HSSFWorkbook workbook){
		return getWorkbookInputStream(workbook);
	}

	public static HSSFWorkbook fillSheet(HSSFWorkbook workbook,ExcelDatas excelDatas) {
		HSSFSheet sheet = workbook.getSheet(excelDatas.getSheetName());
		if(sheet == null){
			sheet = workbook.createSheet(excelDatas.getSheetName());
		}
		List<CellRangeAddress> regions=excelDatas.getRegions();
		if(regions!=null&&regions.size()>0){
			for(CellRangeAddress region:regions){
				sheet.addMergedRegion(region);
			}
		}
		processColumnSelect(sheet, excelDatas);
		processColumnWidth(sheet,excelDatas);
		processRowHeight(sheet,excelDatas);
		fillDatas(workbook,sheet,excelDatas);
		return workbook;
	}

	/**
	 * 处理下拉框
	 * @param sheet
	 * @param excelDatas
	 */
	private static void processColumnSelect(HSSFSheet sheet, ExcelDatas excelDatas) {
		if (excelDatas.getColumnSelect() == null) return ;

		DataValidationHelper helper = sheet.getDataValidationHelper();

		Set<Integer> indexs = excelDatas.getColumnSelect().keySet();
		for (Integer index : indexs) {
			//CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
			CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, index, index);

			//设置下拉框数据
			DataValidationConstraint constraint = helper.createExplicitListConstraint(excelDatas.getColumnSelect().get(index));
			DataValidation dataValidation = helper.createValidation(constraint, addressList);
			dataValidation.setSuppressDropDownArrow(false);
			dataValidation.setEmptyCellAllowed(true);
			dataValidation.setShowPromptBox(true);
			dataValidation.createPromptBox("提示", "只能选择下拉框内的选项");
			sheet.addValidationData(dataValidation);
		}



	}

	/**
	 * 填充数据
	 * @param workbook
	 * @param sheet
	 * @param regions
	 * @param excelDatas
	 */
	private static void fillDatas(HSSFWorkbook workbook, HSSFSheet sheet, ExcelDatas excelDatas) {
//		sheet.setDefaultRowHeightInPoints(10);
//		sheet.setDefaultColumnWidth(10);
		int rowIndex=-1;
		HSSFRow row=null;
		HSSFCell cell = null;
		/*
		 * 处理头文件中的最后一行最后一列
		 */
		int sheetLastRows = sheet.getPhysicalNumberOfRows()-1;
		HSSFRow tempRow=sheet.getRow(sheetLastRows);
		int sheetLastColumn= tempRow==null?-1:tempRow.getPhysicalNumberOfCells()-1;

		List<CellData> cellDatas = excelDatas.getCellDatas(sheetLastRows,sheetLastColumn);
		for(CellData cellData:cellDatas){
			if(cellData.getRow()!=rowIndex){
				rowIndex=cellData.getRow();
				row =sheet.getRow(rowIndex);
				if(row==null){
					row =sheet.createRow(rowIndex);
				}
			}
			cell=row.getCell(cellData.getColumn());
			if(cell==null){
				cell=row.createCell(cellData.getColumn());
			}
			addStyle(workbook,cell,cellData.getCellStyle());
			if(cellData.getData().length()<11&&NumberUtil.isDouble(cellData.getData())){
				cell.setCellType(CellType.NUMERIC);
				if(NumberUtil.isInt(cellData.getData())){
					cell.setCellValue(NumberUtil.getInt(cellData.getData()));
				}else{
					cell.setCellValue(NumberUtil.getDouble(cellData.getData()));
				}
			}else{
				cell.setCellValue(cellData.getData());
			}
		}
	}

	public static InputStream exportExcelWithHead(InputStream is, ExcelDatas excelDatas) {
		HSSFWorkbook workbook = null;
		try {
			workbook = new HSSFWorkbook(is);
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		HSSFSheet sheet = workbook.getSheetAt(0);
		List<CellRangeAddress> regions=excelDatas.getRegions();
		if(regions!=null&&regions.size()>0){
			for(CellRangeAddress region:regions){
				sheet.addMergedRegion(region);
			}
		}
		processColumnWidth(sheet,excelDatas);
		processRowHeight(sheet,excelDatas);
		if(excelDatas!=null){
			fillDatas(workbook, sheet,excelDatas);
		}
		return getWorkbookInputStream(workbook);
	}
	/**
	 * 处理列宽
	 * @param sheet
	 * @param excelDatas
	 */
	private static void processColumnWidth(HSSFSheet sheet,
			ExcelDatas excelDatas) {
		if(excelDatas==null){
			return;
		}
		if(excelDatas.getColumnWidthMap()==null){
			return;
		}
		Set<Entry<Integer, Integer>> set = excelDatas.getColumnWidthMap().entrySet();
		Iterator<Entry<Integer, Integer>> iter =set.iterator();
		Entry<Integer, Integer> entry;
		while(iter.hasNext()){
			entry=iter.next();
			if(entry.getValue()==-1){
				sheet.autoSizeColumn(entry.getKey());
			}else{
				sheet.setColumnWidth(entry.getKey(), entry.getValue());
			}
		}
	}
	/**
	 * 处理列宽
	 * @param sheet
	 * @param excelDatas
	 */
	private static void processRowHeight(HSSFSheet sheet,
			ExcelDatas excelDatas) {
		if(excelDatas==null){
			return;
		}
		if(excelDatas.getRowHeightMap()==null){
			return;
		}
		Set<Entry<Integer, Integer>> set = excelDatas.getRowHeightMap().entrySet();
		Iterator<Entry<Integer, Integer>> iter =set.iterator();
		Entry<Integer, Integer> entry;
		while(iter.hasNext()){
			entry=iter.next();
			if(entry !=null){
				HSSFRow row =sheet.getRow(entry.getKey());
				if(row == null){
					row = sheet.createRow(entry.getKey());
				}
			    row.setHeight(entry.getValue().shortValue());
			}
		}
	}
	public static InputStream exportExcelWithHead(String headPath, ExcelDatas excelDatas) {
		return exportExcelWithHead(getExcelStream(headPath),excelDatas);
	}

	public static <T> List<T> getListFromExcel(String path, Class<? extends T> clazz,
			String[] columns, boolean[] notNull, int startRow,
			ExcelFormatListener listener) throws Throwable {
		List<T> list = new ArrayList<T>();

		TypeConverter typeConverter = ApplicationContext.get(TypeConverter.class);
		HSSFCell currentCell = null;
		int currentRow=0;
		int currentCol=0;
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(path);
			POIFSFileSystem fs = new POIFSFileSystem(fis);
			// 创建工作簿
			HSSFWorkbook workBook = new HSSFWorkbook(fs);
			/**
			 * 获得Excel中第一个工作表
			 */
			HSSFSheet sheet = workBook.getSheetAt(0);
			int rows = sheet.getLastRowNum();//getPhysicalNumberOfRows();没有空行的实际行数 // 获得行数这里改为了最后一行有数据的行数

			boolean isNull;
			ArrayList<NullCellException> exceptions = new ArrayList<NullCellException>();
		//	int mustNotNullCount = getMustNotNullCount(notNull);
			if (rows >= startRow) {
				sheet.getMargin(HSSFSheet.TopMargin);
				//一行为一个实体的各个字段的内容，一行new出一个新实体
				for (int j = startRow; j <= rows; j++) { // 行循环
					currentRow=j;
					T domain = clazz.newInstance();
					HSSFRow row = sheet.getRow(j);
					//空行跳过
					if(row==null)continue;

					boolean haveData =false;
					//有内容开始循环列
					for (short k = 0; k < columns.length; k++) { // 列循环
						currentCol=k;
						currentCell = row.getCell(currentCol);
						if(!(currentCell==null||currentCell.getCellType() == HSSFCell.CELL_TYPE_BLANK)){
							//一行中只要有一个数据则状态修改为存在数据
							haveData=true;
						}

						isNull=setValueToDomain(clazz, domain, columns[currentCol], currentCell,
								typeConverter, notNull[currentCol],currentRow,currentCol,listener);
						if(isNull){
								exceptions.add(new NullCellException("导入Excel格式错误：第" + (currentRow + 1)
										+ "行第" + (currentCol + 1) + "列不能为空！"));
						}
					}

					/*
					 * 全部为空则为空行，跳过
					 */
					if(!haveData){//&&exceptions.size()==mustNotNullCount
						exceptions.clear();
						haveData=false;
						continue;
					}
					/*
					 * 如果有异常，并且数量不是列长度进行报错
					 */
					if(exceptions.size()>0){
						throw exceptions.get(0);
					}
					/*
					 * 正常情况加，加入到list
					 */
						// add it to collection
					list.add(domain);
				}
			}
		} catch (TypeMismatchException ex) {
			throw new Exception("导入Excel格式错误：第" + (currentRow + 1)
					+ "行第" + (currentCol + 1) + "列格式不符合标准");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
			e.printStackTrace();
		}finally{
			if(fis!=null){
				fis.close();
			}
		}
		return list;
	}
	/**
	 * 得到必须不为空的个数
	 * @param notNull
	 * @return
	 */
	private int getMustNotNullCount(boolean[] notNull) {
		int count=0;
		for(boolean b:notNull){
			if(b){
				count++;
			}
		}
		return count;
	}

	public InputStream exportExcel(List<ExcelDatas> excelDatass) {
		HSSFWorkbook workBook=new HSSFWorkbook();
		for(ExcelDatas datas:excelDatass){
			fillSheet(workBook,datas);
		}
		return getWorkbookInputStream(workBook);
	}


	/**
	 *
	 * 向WorkBook插入图片
	 * @author zy
	 * @param path 可以是本地相对路径，也可以是http路径
	 * @param workbook  HSSFWorkbook 请保证该workbook已创建HSSFSheet ，
	 * 					默认会在第一个HSSFSheet 里插入图片，如果需要指定HSSFSheet ，请使用另一个api
	 * @param sheetIndex 要插入图片的sheet
	 * @param row1 图片左上角单元格的行
	 * @param col1 图片左上角单元格的列
	 * @param row2 图片右下角单元格的行
	 * @param col2 图片右下角单元格的列
	 */
	public static void addPicture(String path, HSSFWorkbook workbook,String sheetName, int row1, int col1, int row2, int col2){
		HSSFSheet sheet = workbook.getSheet(sheetName);
		if(sheet == null){
			sheet = workbook.createSheet(sheetName);
		}
		if(path.startsWith("http")){
			try {
				addPicture(new URL(path), workbook, sheet, row1, col1, row2, col2);
			} catch (MalformedURLException e) {
				e.printStackTrace();
			}
		}else{
			String rootpath = Thread.currentThread().getContextClassLoader().getResource("/").getFile();

			path = rootpath.substring(0,path.indexOf("WEB-INF"))+path;

			addPicture(new File(path), workbook, sheet, row1, col1, row2, col2);
		}
	}


	/**
	 *
	 * 向WorkBook插入图片
	 * @author zy
	 * @param file 图片文件
	 * @param workbook  HSSFWorkbook 请保证该workbook已创建HSSFSheet ，
	 * 					默认会在第一个HSSFSheet 里插入图片，如果需要指定HSSFSheet ，请使用另一个api
	 * @param sheetIndex 要插入图片的sheet
	 * @param row1 图片左上角单元格的行
	 * @param col1 图片左上角单元格的列
	 * @param row2 图片右下角单元格的行
	 * @param col2 图片右下角单元格的列
	 */
	public static void addPicture(File file, HSSFWorkbook workbook,String sheetName, int row1, int col1, int row2, int col2){
		HSSFSheet sheet = workbook.getSheet(sheetName);
		if(sheet == null){
			sheet = workbook.createSheet(sheetName);
		}
		addPicture(file, workbook, sheet, row1, col1, row2, col2);
	}
	/**
	 *
	 * 向WorkBook插入图片
	 * @author zy
	 * @param file 图片文件
	 * @param workbook  HSSFWorkbook 请保证该workbook已创建HSSFSheet ，
	 * 					默认会在第一个HSSFSheet 里插入图片，如果需要指定HSSFSheet ，请使用另一个api
	 * @param sheetIndex 要插入图片的sheet
	 * @param row1 图片左上角单元格的行
	 * @param col1 图片左上角单元格的列
	 * @param row2 图片右下角单元格的行
	 * @param col2 图片右下角单元格的列
	 */
	private static void addPicture(File file, HSSFWorkbook workbook,HSSFSheet sheet, int row1, int col1, int row2, int col2){
		BufferedImage bufferImg = null;
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		String fileType = FileUtil.getFileSuffix(file.getName());
		try{
			//加载图片
			bufferImg = ImageIO.read(file);
			ImageIO.write(bufferImg, fileType, byteArrayOut);
		}catch (Exception e) {
			throw new RuntimeException(e);
		}

		HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
		if(patriarch == null){
			patriarch = sheet.createDrawingPatriarch();
		}
		HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)col1, row1, (short)col2, row2);
		int picType =HSSFWorkbook.PICTURE_TYPE_JPEG;

		if(fileType.equals("jpg") || fileType.equals("jpeg")){
			picType =HSSFWorkbook.PICTURE_TYPE_JPEG;
		}else if(fileType.equals("png")){
			picType =HSSFWorkbook.PICTURE_TYPE_PNG;
		}
		patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(),picType));
	}
	/**
	 *
	 * 向WorkBook插入图片
	 * @author zy
	 * @param file 图片文件
	 * @param workbook  HSSFWorkbook 请保证该workbook已创建HSSFSheet ，
	 * 					默认会在第一个HSSFSheet 里插入图片，如果需要指定HSSFSheet ，请使用另一个api
	 * @param sheetIndex 要插入图片的sheet
	 * @param row1 图片左上角单元格的行
	 * @param col1 图片左上角单元格的列
	 * @param row2 图片右下角单元格的行
	 * @param col2 图片右下角单元格的列
	 */
	private static void addPicture(URL url, HSSFWorkbook workbook,HSSFSheet sheet, int row1, int col1, int row2, int col2){
		BufferedImage bufferImg = null;
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		String fileType = url.getPath().substring(url.getPath().lastIndexOf(".")+1);
		try{
			//加载图片
			bufferImg = ImageIO.read(url);
			ImageIO.write(bufferImg, fileType, byteArrayOut);
		}catch (Exception e) {
			throw new RuntimeException(e);
		}

		HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
		if(patriarch == null){
			patriarch = sheet.createDrawingPatriarch();
		}
		HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)col1, row1, (short)col2, row2);
		int picType =HSSFWorkbook.PICTURE_TYPE_JPEG;

		if(fileType.equals("jpg") || fileType.equals("jpeg")){
			picType =HSSFWorkbook.PICTURE_TYPE_JPEG;
		}else if(fileType.equals("png")){
			picType =HSSFWorkbook.PICTURE_TYPE_PNG;
		}
		patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(),picType));
	}


}
